package com.ruoyi.xuexiao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.xuexiao.domain.entity.RwTzV3;
import com.ruoyi.xuexiao.domain.param.RwTzV3VParam;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface RwTzV3Mapper extends BaseMapper<RwTzV3> {

    /**
     * 填报统计
     * 上半年统计
     */
    @Select("\t\t SELECT\n" +
            "      COALESCE(COUNT(sxbn), 0) AS total_sxbn,\n" +
            "      COALESCE(SUM(CASE WHEN rwzt IN ('4', '5', '6', '7', '8', '9', '2001') THEN 1 ELSE 0 END), 0) AS ytb,\n" +
            "      COALESCE(SUM(CASE WHEN rwzt IN ('1', '2', '3', '1000', '1001', '2000') THEN 1 ELSE 0 END), 0) AS wtb,\n" +
            "      COALESCE(CAST(ROUND(SUM(CASE WHEN rwzt IN ('4', '5', '6', '7', '8', '9', '2001') THEN 1 ELSE 0 END) * 100.0 / COUNT(sxbn), 0) AS INTEGER), 0) AS bfb\n" +
            "FROM\n" +
            "      rw_tz_v3\n" +
            "WHERE\n" +
            "      sxbn = 1;")
    List<RwTzV3VParam> selectRwTzV3Year(RwTzV3VParam rwTzV3DTO);

    /**
     * 填报统计
     * 下半年统计
     */
    @Select("\t\t SELECT\n" +
            "      COALESCE(COUNT(sxbn), 0) AS total_sxbn,\n" +
            "      COALESCE(SUM(CASE WHEN rwzt IN ('4', '5', '6', '7', '8', '9', '2001') THEN 1 ELSE 0 END), 0) AS ytb,\n" +
            "      COALESCE(SUM(CASE WHEN rwzt IN ('1', '2', '3', '1000', '1001', '2000') THEN 1 ELSE 0 END), 0) AS wtb,\n" +
            "      COALESCE(CAST(ROUND(SUM(CASE WHEN rwzt IN ('4', '5', '6', '7', '8', '9', '2001') THEN 1 ELSE 0 END) * 100.0 / COUNT(sxbn), 0) AS INTEGER), 0) AS bfb\n" +
            "FROM\n" +
            "      rw_tz_v3\n" +
            "WHERE\n" +
            "      sxbn = 2;")
    List<RwTzV3VParam> selectRwTzV3(RwTzV3VParam rwTzV3DTO);



    /**
     * 审核统计
     * 上半年统计
     * @param
     * @return
     */
    @Select("SELECT\n" +
            "    COALESCE(COUNT(sxbn), 0) AS total_sxbn,\n" +
            "    COALESCE(SUM(CASE WHEN rwzt = '9' THEN 1 ELSE 0 END), 0) AS ytb,\n" +
            "    COALESCE(SUM(CASE WHEN rwzt IN ('1', '2', '3', '4', '5', '6', '7', '8', '1000', '1001', '2000', '2001') THEN 1 ELSE 0 END), 0) AS wtb,\n" +
            "    COALESCE(CAST((SUM(CASE WHEN rwzt = '9' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(sxbn), 0)) AS INTEGER), 0) AS bfb\n" +
            "FROM\n" +
            "    rw_tz_v3\n" +
            "WHERE\n" +
            "    sxbn = 1;")
    List<RwTzV3VParam> selectRwTz(RwTzV3VParam rwTzV3Vo);

    /**
     * 审核统计
     * 下半年统计
     */
    @Select("SELECT\n" +
            "    COALESCE(COUNT(sxbn), 0) AS total_sxbn,\n" +
            "    COALESCE(SUM(CASE WHEN rwzt = '9' THEN 1 ELSE 0 END), 0) AS ytb,\n" +
            "    COALESCE(SUM(CASE WHEN rwzt IN ('1', '2', '3', '4', '5', '6', '7', '8', '1000', '1001', '2000', '2001') THEN 1 ELSE 0 END), 0) AS wtb,\n" +
            "    COALESCE(CAST((SUM(CASE WHEN rwzt = '9' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(sxbn), 0)) AS INTEGER), 0) AS bfb\n" +
            "FROM\n" +
            "    rw_tz_v3\n" +
            "WHERE\n" +
            "    sxbn = 2;")
    List<RwTzV3VParam> selectRw(RwTzV3VParam rwTzV3Vo);


    /**
     * 常规每月填报情况(上半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb,\n" +
            "\tbr.adnm AS adnm \n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tand rtv.sxbn = '1' " +
            "\tAND rtv.rwzt IN ('4', '5', '6', '7', '8', '9', '2001','1001')\n" +
            "\tAND br.adcd = #{adcd}\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj),br.adnm")
    List<RwTzV3VParam> selectY(String cityName);

    /**
     * 常规每月填报情况(上半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb,\n" +
            "\tbr.adnm AS adnm \n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tand rtv.sxbn = '1' " +
            "\tAND rtv.rwzt IN ('4', '5', '6', '7', '8', '9', '2001','1001')\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj),br.adnm")
    List<RwTzV3VParam> selectBKY();


    /**
     * 常规每月填报情况(下半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb,\n" +
            "\tbr.adnm AS adnm\n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tAND rtv.rwzt IN ('4', '5', '6', '7', '8', '9', '2001','1001')\n" +
            "\tand rtv.sxbn = '2'\n" +
            "\tAND br.adcd = #{adcd}\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj),br.adnm\n")
    List<RwTzV3VParam> selectXy(String cityName);

    /**
     * 常规每月填报情况(下半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb,\n" +
            "\tbr.adnm AS adnm\n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tAND rtv.rwzt IN ('4', '5', '6', '7', '8', '9', '2001','1001')\n" +
            "\tand rtv.sxbn = '2'\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj),br.adnm\n")
    List<RwTzV3VParam> selectKy();


    /**
     * 常规每月审核情况(上半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb,\n" +
            "\tbr.adnm AS adnm\n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tAND rtv.rwzt IN ( '9')\n" +
            "\tand rtv.sxbn = '1'\n" +
            "\tAND br.adcd = #{adcd}\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj),br.adnm\n")
    List<RwTzV3VParam> selectSh(String cityName);

    /**
     * 常规每月审核情况(上半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb\n" +
//            "\tbr.adnm AS adnm\n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
//            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tAND rtv.rwzt IN ( '9')\n" +
            "\tand rtv.sxbn = '1'\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj)\n")
    List<RwTzV3VParam> selectKh();

    /**
     * 常规每月审核情况(下半年)
     */
    @Select("SELECT\n" +
            "\tEXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "\tcount(*) AS ytb,\n" +
            "\tbr.adnm AS adnm\n" +
            "FROM\n" +
            "rw_tz_v3 rtv\n" +
            "LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "WHERE rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "  AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "\tAND rtv.rwzt IN ( '9')\n" +
            "\tand rtv.sxbn = '2'\n" +
            "\tAND rtv.sxzqdm = #{adcd}\n" +
            "GROUP BY EXTRACT(MONTH FROM rtv.rwcjsj),br.adnm\n")
    List<RwTzV3VParam> selectXSh(String cityName);


    /**
     * 常规每月审核情况(下半年)
     */
    @Select("SELECT\n" +
            "    EXTRACT(MONTH FROM rtv.rwcjsj) AS month,\n" +
            "    COUNT(*) AS ytb\n" +
//            "    br.adnm AS adnm\n" +
            "  FROM\n" +
            "    rw_tz_v3 rtv\n" +
//            "    LEFT JOIN basic_region br ON rtv.sxzqdm = br.adcd\n" +
            "  WHERE\n" +
            "    rtv.rwcjsj >= date_trunc('year', CURRENT_DATE)\n" +
            "    AND rtv.rwcjsj < date_trunc('year', CURRENT_DATE) + INTERVAL '6 months'\n" +
            "    AND rtv.rwzt = '9'\n" +
            "    AND rtv.sxbn = '2'\n" +
            "  GROUP BY" +
            "  EXTRACT(MONTH FROM rtv.rwcjsj)")
    List<RwTzV3VParam> selectKSh();

}
